package com.group.zsxm.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Service;

import com.fins.gt.model.FilterInfo;
import com.fins.gt.model.SortInfo;
import com.group.core.common.LimitPage;
import com.group.core.common.Page;
import com.group.zsxm.service.common.BaseService;

@Service
public class JfhsbqueryService extends BaseService{

	public Object getShzfList(LimitPage limit, Map<String, String> parMap, SortInfo sortInfo, List<FilterInfo> filterInfos,String dwid){
		String name = parMap.get("name") == null ? "" :  parMap.get("name");
		String where = parMap.get("where") == null ? "" :  parMap.get("where");
		
		Map jfhsb = new HashMap();
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
			jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		}else{
			return  new Page();
		}
		String dm = (jfhsb.get("dm")==null?"":String.valueOf(jfhsb.get("dm")));
		
		String ls_sql = "" ;
		//ls_sql = "select a.DWMC ,b.* from dw_info_v a,jfhsb_shzf b where 1=1 and a.dwid=b.dwid and a.dwid='"+dwid+"'";
		ls_sql = "select a.RYID,a.XM,a.SFZ,a.SEX_MC,a.BIRTHDAY,a.XL_MC,a.ZC_MC,a.ZJZ,b.DWID as DC,b.SHZZBZ, b.SHZCSJ, b.SHZZZE, b.ZFZFD, b.ZFBTBZ, b.ZFZCSJ, b.ZFBTZE," +
		"b.ZFZFD2, b.ZFBTBZ2, b.ZFZCSJ2, b.ZFBTZE2, b.ZE, b.SBXX, b.BZ, b.HTYY from dw_ryxx_v a , " +
		" ( select * from jfhsb_shzf where sbdm='"+dm+"0001' and dwid='"+dwid+"' ) b where a.ryid=b.ryid " +
				"  and  a.dwid='"+dwid+"' and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (1,2)) ";
		
		if(where != null && !where.equals("")){
			ls_sql += " and upper("+where+") like '%" + name.toUpperCase() + "%'";
		}
		
		String sortFieldName = "";
		if(sortInfo != null ){
			sortFieldName = sortInfo.getFieldName();
		}
		String sortCond = getGridSortString(sortInfo);
		String filterCond = getGridFilterString(filterInfos);
		if (filterCond != null) {
			ls_sql += filterCond;
		}
		if (sortCond != null ) {
			if(sortFieldName.equals("B_NL")){
				ls_sql += " order by birthday " ;
			}else{
				ls_sql += " order by " + sortCond.split("_")[0];
			}
		} else {
			ls_sql += " order by a.ryid desc ";
		}
		if (limit != null) {
			return this.queryForListWithPage(ls_sql, limit);
		} else {
			return this.simpleJdbcTemplate.queryForList(ls_sql);
		}
	}
	
	


	public Object getAjfList(LimitPage limit, Map<String, String> parMap, SortInfo sortInfo, List<FilterInfo> filterInfos,String dwid){
		String name = parMap.get("name") == null ? "" :  parMap.get("name");
		String where = parMap.get("where") == null ? "" :  parMap.get("where");
		
		Map jfhsb = new HashMap();
		String sql = " select count(*) from jfhsb a where a.bz=1 and len(dm)=4 ";
		if(this.getSimpleJdbcTemplate().queryForInt(sql) > 0){
			sql = " select * from jfhsb a where a.bz=1 and len(dm)=4 ";
			jfhsb = this.getSimpleJdbcTemplate().queryForMap(sql);
		}else{
			return  new Page();
		}
		String dm = (jfhsb.get("dm")==null?"":String.valueOf(jfhsb.get("dm")));
		
		String ls_sql = "" ;
		//ls_sql = "select a.DWMC,b.* from dw_info_v a,jfhsb_ajf b where 1=1 and a.dwid=b.dwid and a.dwid='"+dwid+"' ";
		
		ls_sql = "select a.*,b.DWID as DC,convert(varchar,GFSJ,23) GFSJ, ZZBZ, GFDD, FWMJ, FWZE," +
		"convert(varchar,JFSJ,23) JFSJ, DYNBT, BZ, HTYY" +
		" from dw_ryxx_v a , " +
		" ( select * from jfhsb_ajf where sbdm='"+dm+"0002' and dwid='"+dwid+"' ) b where a.ryid=b.ryid " +
		"  and  a.dwid='"+dwid+"'  and a.ryid in (select distinct ryid from DW_RYXX_JFHTYPE where dwid="+dwid+" and jfhtype in (3))  ";

		
		if(where != null && !where.equals("")){
			ls_sql += " and upper("+where+") like '%" + name.toUpperCase() + "%'";
		}
		
		String sortFieldName = "";
		if(sortInfo != null ){
			sortFieldName = sortInfo.getFieldName();
		}
		String sortCond = getGridSortString(sortInfo);
		String filterCond = getGridFilterString(filterInfos);
		if (filterCond != null) {
			ls_sql += filterCond;
		}
		if (sortCond != null ) {
			if(sortFieldName.equals("B_NL")){
				ls_sql += " order by birthday " ;
			}else{
				ls_sql += " order by " + sortCond.split("_")[0];
			}
		} else {
			ls_sql += " order by a.ryid desc ";
		}
		if (limit != null) {
			return this.queryForListWithPage(ls_sql, limit);
		} else {
			return this.simpleJdbcTemplate.queryForList(ls_sql);
		}
	}
	
	
}
